/// ****************************************************************************
/// Author:	        Robin Zhu
/// Create Date:	2011-11-1
/// Purpose: 		SQL语句编码类（Microsoft SQL Server版本）文件
/// ****************************************************************************
/// Modify By		Date			Remark
/// Robin           2012-3-23       增加对常量为IEnumerable接口类型时的支持
/// Robin           2012-6-14       增加SQL版本的SkipRecords实现
/// Robin           2012-7-25       增加Count函数的编码支持
/// Robin           2012-7-25       增加对Except, Concat(Union All), Union, Intersect的支持
/// Robin           2012-8-14       将byte[]编码为二进制格式
/// Robin           2013-4-30       修改SkipRecords的实现，使返回的字段不包含ROW_NUMBER
/// Robin           2013-5-16       增加对ToUpper, ToLower, Length函数的支持
/// Robin           2014-4-3        增加对Abs函数的支持
/// Robin           2015-12-1       增加对按位非的支持
/// ****************************************************************************

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Globalization;
using System.Linq.Expressions;
using System.Collections;
using System.Text.RegularExpressions;

namespace RB.DataAccess.SqlServer
{
    /// <summary>
    /// SQL语句编码类（Microsoft SQL Server版本）
    /// </summary>
    public class MsSqlEncoder : SqlEncoder
    {
        private static readonly DateTime MinDateTimeValue = DateTime.ParseExact("1753-01-01", "yyyy-MM-dd", CultureInfo.InvariantCulture);

        public override string GetBooleanExpression(string propertyName)
        {
            return "ISNULL(" + propertyName + ",0)=1";
        }

        public override string GetBooleanExpression(bool value)
        {
            if (value)
                return "1=1";
            else
                return "1=0";
        }


        public override string GetOperator(Type operandType, ExpressionType expressionType, bool isNullValue, out bool isMethodCall)
        {
            isMethodCall = false;
            switch (expressionType)
            {
                case ExpressionType.Equal:
                    if (isNullValue)
                        return "IS";
                    return "=";
                case ExpressionType.AndAlso:
                    return "AND";
                case ExpressionType.Not:
                    if (operandType == typeof(bool))
                        return "NOT";
                    return "~";
                case ExpressionType.OrElse:
                    return "OR";
                case ExpressionType.GreaterThan:
                    return ">";
                case ExpressionType.LessThan:
                    return "<";
                case ExpressionType.NotEqual:
                    if (isNullValue)
                        return "IS NOT";
                    return "<>";
                case ExpressionType.GreaterThanOrEqual:
                    return ">=";
                case ExpressionType.LessThanOrEqual:
                    return "<=";

                case ExpressionType.Add:
                    return "+";
                case ExpressionType.Subtract:
                    return "-";
                case ExpressionType.Multiply:
                    return "*";
                case ExpressionType.Divide:
                    return "/";
                case ExpressionType.And:
                    if (operandType == typeof(bool))
                        return "AND";
                    return "&";
                case ExpressionType.Or:
                    if (operandType == typeof(bool))
                        return "OR";
                    return "|";
                case ExpressionType.ExclusiveOr:
                    return "^";
                case ExpressionType.Coalesce:
                    isMethodCall = true;
                    return "ISNULL";
                default:
                    throw new NotSupportedException(string.Format("Not Supported Operator: {0}", expressionType));
            }
        }

        public override string GetFieldName(string propertyName, string tableAlias)
        {
            if (tableAlias != null)
            {
                return tableAlias + ".[" + propertyName + "]";
            }
            else
            {
                return "[" + propertyName + "]";
            }
        }

        public override string GetTableName(string typeName, string tableAlias)
        {
            if (tableAlias != null)
            {
                return "[" + typeName + "] AS " + tableAlias;
            }
            else
            {
                return "[" + typeName + "]";
            }
        }

        /// <summary>
        /// 将一个对象的值转换为SQL形式
        /// </summary>
        /// <param name="value">对象实例</param>
        /// <returns>对象值的SQL形式</returns>
        public override string GetConstant(object value)
        {
            if (value == null)
                return "NULL";
            Type t = value.GetType();
            string valueStr = "";
            if (t == typeof(string))
                valueStr = EncodeString(value as string, true);
            else if (t == typeof(DateTime))
                valueStr = EncodeDateTime((DateTime)value);
            else if (t == typeof(bool))
            {
                valueStr = (bool)value ? "1" : "0";
            }
            else if (t == typeof(DBNull))
                valueStr = "NULL";
            else if (t == typeof(Guid))
                valueStr = "'" + ((Guid)value).ToString() + "'";
            else if (t.IsEnum)
            {
                valueStr = ((int)value).ToString(CultureInfo.InvariantCulture);
            }
            //else if (t.IsArray)
            //{
            //    // 处理变量是数组的情况
            //    var array = (Array)value;
            //    if (array.Length > 0)
            //    {
            //        valueStr = "(";
            //        valueStr += GetConstant(array.GetValue(0));
            //        for (int i = 1; i < array.Length; i++)
            //        {
            //            valueStr += ",";
            //            valueStr += GetConstant(array.GetValue(i));
            //        }
            //        valueStr += ")";
            //    }
            //}
            else if (value is byte[])
            {
                valueStr = "0x" + BitConverter.ToString((byte[])value).Replace("-", "");
            }
            else if (t.IsArray || t.IsClass && value is IEnumerable)
            {
                // 处理变量是可遍历的类型
                var ival = ((IEnumerable)value).GetEnumerator();
                if (ival.MoveNext())
                {
                    valueStr = "(";
                    while (true)
                    {
                        valueStr += GetConstant(ival.Current);
                        if (!ival.MoveNext())
                            break;
                        valueStr += ",";
                    }
                    valueStr += ")";
                }
            }
            else if (value is decimal)
            {
                valueStr = ((decimal)value).ToString(CultureInfo.InvariantCulture);
            }
            else if (value is double)
            {
                valueStr = ((double)value).ToString(CultureInfo.InvariantCulture);
            }
            else if (value is float)
            {
                valueStr = ((float)value).ToString(CultureInfo.InvariantCulture);
            }
            else
                valueStr = value.ToString();
            //if (valueStr == "''")
            //    return "NULL";
            return valueStr;
        }

        private static string EncodeString(string fieldValue, bool quote)
        {
            fieldValue = fieldValue.Replace("'", "''");
            if (quote)
            {
                fieldValue = "N'" + fieldValue + "'";
            }
            return fieldValue;
        }

        private static string EncodeDateTime(DateTime dateTime)
        {
            if (dateTime == DateTime.MinValue)
            {
                return "NULL";
            }
            else
            {
                if (dateTime.Date < MinDateTimeValue)
                {
                    return "'" + dateTime.ToString("HH:mm:ss.fff", CultureInfo.InvariantCulture) + "'";
                }
                else if (dateTime.TimeOfDay == TimeSpan.Zero)
                {
                    return "'" + dateTime.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture) + "'";
                }
                return "'" + dateTime.ToString("yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture) + "'";
            }
        }

        /// <summary>
        /// 将方法调用翻译为SQL语句
        /// </summary>
        /// <param name="method">方法名称</param>
        /// <param name="caller">调用者的SQL语句</param>
        /// <param name="arguments">参数的SQL语句</param>
        /// <param name="callerType">调用者类型</param>
        /// <returns>方法调用的SQL语句</returns>
        public override string GetMethodCall(string method, string caller, string[] arguments, Type callerType)
        {
            if (method == "Equals")
            {
                return caller + "=" + arguments[0];
            }
            else if (method == "StartsWith")
            {
                return caller + " LIKE " + arguments[0] + "+'%'"; 
            }
            else if (method == "EndsWith")
            {
                return caller + " LIKE '%'+" + arguments[0];
            }
            else if (method == "Contains")
            {
                if (callerType != typeof(string))
                {
                    if (string.IsNullOrEmpty(caller) && arguments.Length > 1)
                    {
                        // 用于支持IEnumerable.Contains类型的调用
                        // 如果用于匹配的值表为空，则返回恒假表达式
                        if (string.IsNullOrEmpty(arguments[0]))
                        {
                            return "1=0";
                        }
                        return arguments[1] + " IN " + arguments[0];
                    }
                    else
                    {
                        // 用于支持Array.Contains类型的调用
                        // 如果用于匹配的值表为空，则返回恒假表达式
                        if (string.IsNullOrEmpty(arguments[0]))
                        {
                            return "1=0";
                        }
                        return arguments[0] + " IN " + caller;
                    }
                }
                else
                {
                    return caller + " LIKE '%'+" + arguments[0] + "+'%'";
                }
            }
            else if (method == "HasFlag")
            {
                return caller + " & " + arguments[0] + " = " + arguments[0];
            }
            else if (method == "CompareString")
            {
                return "CHARINDEX(" + arguments[0] + "," + arguments[1] + ")+CHARINDEX(" + arguments[1] + "," + arguments[0] + ")-2";
            }
            else if (method == "Count")
            {
                return "COUNT(" + arguments[0] + ")";
            }
            else if (method == "Except")
            {
                return "EXCEPT";
            }
            else if (method == "Concat")
            {
                return "UNION ALL";
            }
            else if (method == "Union")
            {
                return "UNION";
            }
            else if (method == "Intersect")
            {
                return "INTERSECT";
            }
            else if (method == "ToUpper")
            {
                return "UPPER(" + caller + ")";
            }
            else if (method == "ToLower")
            {
                return "LOWER(" + caller + ")";
            }
            else if (method == "Length")
            {
                return "LEN(" + caller + ")";
            }
            else if (method == "Abs")
            {
                return "ABS" + arguments[0];
            }
            else
                throw new NotSupportedException("Not Supported Method Call: " + method);
        }

        public override string GetTopN(int takeCount, string query)
        {
            return "TOP " + takeCount.ToString(CultureInfo.InvariantCulture) + " " + query;
        }

        public override string SkipRecords(int skipCount, string beforeSkip, string orderClause)
        {
            if (string.IsNullOrEmpty(orderClause))
            {
                orderClause = "ORDER BY (SELECT 1)";
            }
            string rownum = "ROW_NUMBER() OVER(" + orderClause + ") AS ROW_NUMBER";
            string fields = beforeSkip.Remove(beforeSkip.IndexOf("FROM"));
            Regex regEx = new Regex(@"[\.A-Za-z0-9_\[\]]+\sAS\s(?=[a-zA-Z0-9_]+[\,\s])");
            fields = regEx.Replace(fields, "");
            string afterSkip = fields + "FROM (SELECT TOP (100) PERCENT " + rownum + "," + beforeSkip + ") AS AFTER_SKIP_VIEW WHERE ROW_NUMBER > " + skipCount.ToString();
            return afterSkip;
        }
    }
}
